Featured
-
Find out a particular column throughout the database in Postgresql
Previously got a situation where I needed to get t
by Nitika.Verma
Tags
What is MSBI and Its Advantages?
MSBi is an extraordinary business intelligence suite that can provide several ultimate solutions to execute data mining and business queries. It can provide various types of data access to the companies with the help of which they can make variou...
Trigger Execution Order Fired Under Different Events in SQL Server
The automatic execution of stored programs when a specific event occurs are triggers. Triggers are database object binded to a table and are called implicitly. They find their usage while accessing and checking data before and after DDL and DML q...
SQL Server : Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT
Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT
@@IDENTITY
Syntax : SELECT @@IDENTITY
The keyword "@@IDENTITY" in current session is capable of returning newly inserted or last r...
SQL SERVER Case Sensitive SQL Query Search using Collate
SQL SERVER – Case Sensitive SQL query search using Collate
SQL SERVER is not case sensitive therefore there are scenarios in which SQL query search may provide same result for various inputs which only differ with respect to ...
SQL SERVER: Copy structure of an existing table without data into new table
SQL SERVER: Copy structure of an existing table without data into new table
Create a table named "tblStudents" in database and insert some dummy data into it.
CREATE TABLE tblStudents
(
StudentId INT NO...
Auto Generated Auto Incremented Alphanumeric Sequential Code in SQL SERVER
Create a table tbEmployeeMaster by following query :-
CREATE TABLE tbEmployeeMaster
(
EmpId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(100),
EmpCode VARCHAR(15)
)
...
SQl Server : Check table existence
A common problem encountered during SQL server development is to check if a particular table exists or not. There are different approaches to solve this problem and in this blog we will list out these approaches. For illustration purpose we will ...
SQl Server : Get size of tables in database
Many times when we manage SQL server databases we need to determine how much space each table is consuming on disk. In this blog we will learn two approaches for solving this frequently encountered common problem.
Approach 1:
We...
Different Types of SQL Keys
SQL Keys play a very important role in database related task in SQL like retrieve data/record from the table according to the requirement. A key can be defined as a single or combination of multiple fields/columns in a table. With the help ...
SQL Inbuilt Functions
Hi Readers,
In this blog, we will discuss the inbuilt functions of SQL which are used to perform calculations on data. All inbuilt functions are provided to make the task easier to do and we can easily complete a complex task with the help...
SQL Server : Best Practices
Following is a list of best practices for SQL Server
1) Use correct formatting
Following is a sample select query for reference:
SELECT
X.column...
Avoid Concat function in SQL
While writing queries in stored procedures we manipulate with the string data type columns in which concatenating string is very common.
So we all are very familiar with the function CONCAT provided by the SQL Server for joining/merg...
DATEADD function in SQL
While manipulating with the date and time data of the SQL columns we have requirements where we want to alter the date or time based on some condition or for checking validation in our application.
For doing such kind of operation in...
What is STUFF in SQL Server
STUFF():-
STUFF is SQL SERVER function which is used to insert the string in another string. It deletes the specified number of characters from first string and insert the new string in the plac...
What is COALESCE in SQL Server
COALESCE():-
COALESCE is a function in SQL Server which returns the first non NULL expression amongst its parameter. If all the parameters are evaluated to NULL, It will return NULL
Syntex:-
CO...
Limit Keyword in SQL
By default, all results that satisfy the conditions specified in the SQL statement are returned. However, this may not always be what we want, as sometimes we only want to retrieve a subset of records.
In MySQL, this is accomplished ...
What is NOWAIT in SQL Server
Today i will discuss one interesting topic NOWAIT in SQL Server and would explain how to use it.
As we know about the transaction in SQL it is unit of work which is to be executed as whole. Suppose we are updating some rec...
what is NOLOCK in SQL Server
Hi guys, I want to tell you the use of NOLOCK with simple example but before this lets discuss some terms
Lock:-
When multiple users or applications access the non-shareable resource at the same time, Locking allows to access the ...
SQL datatype
SQL data type is an attribute that tell us the type of the object.
SQL server has 6 type of datatype
Exact numeric data type
1. bigint- Range start from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
2. int- Range start from ...
Differece between ROW_NUMBER(), RANK() and DENSE_RANK() in SQL Server
ROW_NUMBER()
RANK()
DENSE_RANK()
All of these three functions are used to calculate the Id of row but in different way.
I am using the below script for examples
CREATE TABLE Marks
(
SubjectId INT,
Marks INT
)...
What is view in Sql server
A View is a virtual table which is created form another table and its content are defined by a query. In real scenarios we use views for security purpose by allowing users to access data through the view, without granting the users permissions to...
Binary Relationship
A Binary relationship is when there is relation between two entities.This relationship is further divided into three types.
One to One
One to Many
Many to one
Many to Many
1.One to One: In this relationship, one entity can be assoc...
SQL COMMANDS
SQL is Structure Query Language which is developed by E.F.Codd.Today almost all Relational Database Management Systems (RDBMS) uses SQL as the standard database language. We can use it to do different types of operations in RDBMS.
SQL COMMAND...
SQL Server : Read only databases
Databases whose data is not required to be changed should be considered to be set as READ ONLY.Databases can be set to READ ONLY mode and back using T-SQL and SSMS.
Following are the scripts that can be used to set database read only and bac...
SQL Server : Useful datetime functions to find specific dates
Following are some useful SQL Server Date functions to find specific dates.
1) Today
SELECT GETDATE() 'Today'
2) Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
3) First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk...
SQL Server : Global Variables
SQL Server provides a number of global variables, which are very useful.The values of these variables is maintained by the server. All the global variables represent information specific to the server or a current user session.The names of global...
SQL Server : How to Enable or Disable All the Triggers on a Table and Database?
Sometimes we need to disable trigger on a table especially when performing admin tasks on a table. Following commands provide a quick way to disable all the triggers for a table. Please note
that when we are diabling triggers on a table we w...
SQL Server : How to do case sensitive search?
Sometimes during application development we need to do case sensitive search. Let us see how we can do the same in SQL Server.
For illustration purpose we will use a table called Technology which has a column named Platform.Now let us say colu...
SQL : How to update top N records in a table?
It is a common requirement in SQL server development to update top N records in SQL server.In this blog we will see two approaches to accomplish the same.
1) Using UPDATE with TOP
UPDATE TOP (100) Table1 SET field1 = 1
However witho...
SQL Server : Error handling using TRY-CATCH
Before SQL Server 2005, the only practical way to trap errors in SQL was using the old-fashioned @@error system variable. Although this is still supported, in this blog we will learn how to use TRY-CATCH for handling errors.TRY-CATCH block is si...
SQL Server Transactions
A Transaction groups a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully finish.If all of these tasks execute successfully, then a transaction is committe...
SQL: Difference between inner join and outer join
Joins are used to combine the data from two tables and return specific rows of data from the tables.
A join can be either an inner join or an outer join, depending on what is expected in the result.
INNER JOIN:
Gets all matching rows i...
To Run Stored Procedure everyday
For doing that we need to create procedure first and also define the repeating interval to call it again
We will create procedure named getnotification from the particular database that gets fired after 3:30 hours on a daily basis.
Wh...
SQL : How to get last N records based on ordering of a specific column in SQL Server ?
Sometimes we may be required to get the get last N records of a table in SQL server based on ordering on a specific column . In this post we will create a query to get the result. First let us create a table which we will use in our query.
CR...
SQL : How to reset identity seed after deletion of records in SQL server ?
By using the seed value for every new record Microsoft SQL Server's identity column generates sequential values. In this post we will learn how to reseed an identity column.
Following is the command to reset the identity property :
DBCC C...
SQL : How to get column names of a table in SQL Server ?
Often we may be required to get the names of all columns in a table in SQL server. In this post we will see the different options available in SQL server to fetch the column names.
1) We can use the below query to get the column names.
SELE...
SQL : How to Delete using INNER JOIN in SQL Server ?
In this post we will see how to use JOIN for deleting data from SQl server table. Let us first create tables which we will use for understanding the deletion process using JOINS.
-- Create table1
CREATE TABLE #Table1 (Col1 INT, Col2 VARCHAR(5...
SQL : How to check if a stored procedure exists before creation?
In this article we will learn how to check if a stored procedure exists before creating it. We can use the below script which will drop the proc if it exists and then recreate it.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name...
SQl : How to replace a string in SQL server column ?
We know that the data can be changed inside a field by using update command.
In this article I will guide you to replace part of the data without changing the entire data of the field. For this we will use replace command to replace a part of t...
Encryption in SQL
For providing security and privacy we need to store data in encrypted form.
In SQL this can be done with series of steps:
CREATE TABLE dbo.Employees(
EmployeeID int primary key,
EmployeeName varchar(50) NULL,
n...
import assemblies in SQL
In SQL Server there are situations when you need to import assemblies to perform a specific task
For doing that you need to write the path where assembly is present and provide the permissions to the assembly and associating with the particu...
To find duplicate records in SQL
In SQL if we dont have used primary keys then our id gets repeated accidentally or unintentionally.
In this case we need to find out how many records are same. We need to find out the same tuples or rows from a table.
For doing that...
SQL : How to pass an array of parameters to stored procedure using XML?
In this article we will see how to pass an array of parameters to a stored procedure using xml. For illustration purpose we will be passing the
below list of id's :
<ids>
<id>1</id>
<id>2</id>
<...
SQL : How to get duplicate rows based on specific fields in table ?
In the following article we will go through a solution to a very common requirement of getting duplicate rows from SQL Server table based on specific columns.
Let us first create a table and add sample data to this table. Col1 in the table is an...
SQL : How to remove duplicates in SQL table?
In the following article we will go through a solution to a very common requirement of removing delete duplicate rows from SQL server table.
Let us first create a table and add sample data to this table. Col1 in the table is an identity column.
...
SQL : How to get count of duplicate records?
In the following article we will go through a solution to a very common requirement of getting count of duplicate rows from SQL server table.
Let us first create a table and add sample data to this table. Col1 in the table is an identity column....
APEX SQL
APEX SQL
APEX in SQL is a product from Microsoft that is used for synching of data stored on different servers.
We have our main data stored in a main server and replica of those data are stored on different servers.
Consider...
Temporary Tavles in SQL
Temporary tables in SQL are used for manipulation of data for a short period of time.
It can be used as a accumulator like we have in microprocessor which acts as mediator to store and perform operations into it.
Temporary table in ...
SQL : How to get Nth record in SQL Table ?
In the following article we will go through a solution to a very common requirement of getting Nth record in a SQL server table.
Let us first create a table and add sample data to this table. Col1 in the table is an identity column.
CREATE TA...
SQL : How to find all tables containing column with specific name ?
Many times we come across a requirement of finding names of all tables which contain specific columns. We can get the answer by using the below query:
SELECT COL.name AS ColumnName, TAB.name AS TableName
FROM sys.columns COL
JOIN sys.tab...
